-- @owner: opentestcase040
-- @date: 2022-05-11
-- @testpoint: 存储过程中使用insert into

--step1:建表;expect:成功
drop table if exists t_FVT_INSERT_0006;
create table t_FVT_INSERT_0006 (id int,name varchar2(20)) with (storage_type=ustore);

--step2:循环插入数据使用insert into结合updata;expect:成功
declare
i int:=0;
begin
  loop
    i:=i+1;
        insert into t_FVT_INSERT_0006 (id,name) values('1','xxy');
        update t_FVT_INSERT_0006 set name = 'tt';
        exit when i=1000;
  end loop;
  raise info '%',('111');
end;
/

--step3:查询表t_FVT_INSERT_0006数据结合聚合函数;expect:count返回值为1000
select count(*) from t_FVT_INSERT_0006;

--step4:建序列;expect:成功
drop sequence if exists s_FVT_INSERT_0006;
create sequence s_FVT_INSERT_0006 increment by 1 start with 1;

--step5:建表t_FVT_INSERT_0006_1;expect:成功
drop table if exists t_FVT_INSERT_0006_1;
create table t_FVT_INSERT_0006_1(id number,qq number,ww number) with (storage_type=ustore);
insert into t_FVT_INSERT_0006_1 values(s_FVT_INSERT_0006.nextval,1,1);
--step6:循环插入数据利用序列;expect:成功
declare
i int:=0;
begin
  loop
    i:=i+1;
    insert into t_FVT_INSERT_0006_1 values(s_FVT_INSERT_0006.nextval,1,1);
    exit when i=1000;
  end loop;
  raise info '%',('111');
end;
/

--step7:查询表t_FVT_INSERT_0006数据结合聚合函数;expect:count返回值为1001
select count(*) from t_FVT_INSERT_0006_1;

--step8:环境清理;expect:成功
drop table t_FVT_INSERT_0006;
drop table t_FVT_INSERT_0006_1;
drop sequence s_FVT_INSERT_0006;
